"""
设备相关接口
"""
import math
import time
import datetime
import pandas as pd
from io import BytesIO
from flask import request, Blueprint, send_file, render_template, current_app
from sqlalchemy import select, func
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from app.config import DevelopmentConfig
from model import model
from src.core import ack_publish
from src.utils import response_, verification

devices = Blueprint('devices', __name__, url_prefix='/devices')

matt_ack = ack_publish.MQTTServer()


@devices.route('/get/data/temp_humidity', methods=['GET'])
def get_data_temp_humidity():
    """ 获取设备温湿度 """
    query = request.args.get('topic')
    temp = matt_ack.equip_temperature(topic=query)
    data = {
        "temp_humidity": temp,
    }
    return response_.success(data=data)


@devices.route('/get/data/devicesInfo', methods=['GET'])
@verification.token_required
def get_devices_info(current_user):
    """ 获取设备信息 """
    try:
        page = request.args.get('page', default=1, type=int)
        size = request.args.get('size', default=10, type=int)
        start_str = request.args.get('start_time')
        end_str = request.args.get('end_time')
        device_name = request.args.get('device_name')
        mac = request.args.get('mac')
        eq_type = request.args.get('eq_type')
        verifications = request.args.get('verification')

        if page < 1:
            return response_.error(message="page参数必须大于等于1", code=400)

        if size < 1 or size > 100:
            return response_.error(message="size参数必须在1到100之间", code=400)
            # 处理时间筛选逻辑
        filters = []
        if start_str or end_str:
            try:
                # 转换日期字符串并计算时间戳范围
                start_date = datetime.datetime.strptime(start_str, '%Y-%m-%d')
                end_date = datetime.datetime.strptime(end_str, '%Y-%m-%d')

                # 计算起始时间戳 (当天第一毫秒)
                start_timestamp = int(start_date.timestamp() * 1000)
                # 计算结束时间戳 (当天最后一毫秒)
                end_timestamp = int(
                    (end_date + datetime.timedelta(days=1)).timestamp() * 1000
                ) - 1

                # 添加时间筛选条件
                filters.append(
                    model.EqInfo.power_ontime.between(start_timestamp, end_timestamp)
                )

            except ValueError:
                return response_.error(
                    message="日期格式错误，请使用YYYY-MM-DD格式",
                    code=400
                )
                # 处理设备名称筛选 (新增逻辑)
        if device_name:
            # 添加设备名称模糊匹配条件
            filters.append(
                model.EqInfo.device_name.ilike(f'%{device_name}%')  # 使用ilike进行不区分大小写的模糊匹配
            )

        # 处理MAC地址筛选 - 精确查询
        if mac:
            # 可选：对MAC地址进行标准化处理
            # 如去除分隔符，统一大小写等
            normalized_mac = mac.strip().replace(':', '').replace('-', '').upper()
            filters.append(
                func.upper(func.replace(func.replace(model.EqInfo.mac, ':', ''), '-', '')) == normalized_mac
            )

        # 处理设备类型筛选 - 精确查询 (汉字匹配)
        if eq_type:
            # 设备类型是汉字，进行精确匹配
            filters.append(
                model.EqInfo.eq_type == eq_type.strip()
            )

        # 处理验证码筛选 - 精确查询 (数字)
        if verifications:
            try:
                verifications_int = int(verifications)
                filters.append(
                    model.EqInfo.verification == verifications_int  # 精确匹配
                )
            except ValueError:
                return response_.error(
                    message="验证码参数必须是有效的整数值",
                    code=400
                )
        # 构建基础查询
        stmt = select(model.EqInfo)
        if filters:
            stmt = select(model.EqInfo).where(*filters)

        stmt = stmt.order_by(model.EqInfo.power_ontime.desc())

        with Session(DevelopmentConfig.engine) as session:
            # 获取总记录数
            count_stmt = select(func.count()).select_from(stmt.subquery())
            total_count = session.scalar(count_stmt)

            # 计算分页信息
            total_pages = max(1, math.ceil(total_count / size))

            # 获取当前页数据
            offset = (page - 1) * size
            paginated_stmt = stmt.offset(offset).limit(size)
            device_list = session.scalars(paginated_stmt).all()
            # 转换为字典
            devices_data = [device.to_dict() for device in device_list]

            # 构建响应
            response = {
                "list": devices_data,
                "count": total_count,
                "pagination": {
                    "page": page,
                    "size": size,
                    "total_pages": total_pages,
                    "has_previous": page > 1,
                    "has_next": page < total_pages
                }
            }
            return response_.success(data=response)
    except Exception as ex:
        return response_.error(message=str(ex), code=500)


@devices.route('/add', methods=['POST'])
@verification.token_required
def add_device(current_user):
    """新增设备信息
    必填项: mac, power_ontime, sim_card_num, verification
    """
    session = None
    data = request.get_json()
    try:
        if not data:
            return response_.error(message="请求体不能为空", code=400)

        # 检查必填字段
        required_fields = ['mac', 'sim_card_num']
        missing = [field for field in required_fields if field not in data]
        if missing:
            return response_.error(
                message=f"缺少必填字段: {', '.join(missing)}",
                code=400
            )

        # 准备设备数据
        device_data = {
            'mac': data['mac'],
            'power_ontime': int(time.time() * 1000),
            'sim_card_num': data['sim_card_num'],
            'verification': verification.generate_number(),
            'add_time': int(time.time() * 1000),  # 当前时间戳（毫秒）
            'status': 1  # 首次上电即为在线
        }

        # 处理可选字段
        optional_fields = [
            'device_name', 'eq_type', 'things_cart_num', 'product_id',
            'version', 'cate', 'source', 'owner', 'breeding_base', 'plot'
        ]
        for field in optional_fields:
            if field in data:
                device_data[field] = data[field]

        # 创建设备并保存到数据库
        with Session(DevelopmentConfig.engine) as session:
            device = model.EqInfo(**device_data)
            session.add(device)
            session.commit()

            # 返回创建成功的设备信息
            return response_.success(
                message="设备创建成功",
                data=device.to_dict(),
                code=201
            )

    except IntegrityError as e:
        # 回滚事务
        if session:
            session.rollback()

        error_message = str(e)
        # 检查是否是因为mac重复
        if 'mac' in error_message.lower() and (
                'unique' in error_message.lower() or 'duplicate' in error_message.lower()):
            return response_.error(
                message=f"该MAC地址 '{data['mac']}' 已经存在",
                code=409
            )
        else:
            # 其他唯一键冲突，返回一个通用错误
            return response_.error(
                message="数据唯一性冲突，请检查提交的数据",
                code=409
            )
    except Exception as e:
        if 'session' in locals() and session:
            session.rollback()
        return response_.error(message=str(e), code=500)

    finally:
        if session:
            session.close()


@devices.route('/category/list', methods=['GET'])
@verification.token_required
def list_categories(current_user):
    """ 获取设备类别 """
    # 获取查询参数并设置默认值
    page = request.args.get('page', default=1, type=int)
    size = request.args.get('size', default=10, type=int)
    category_name = request.args.get('category_name', type=str)
    status = request.args.get('status', type=str)

    # 验证分页参数
    if page < 1 or size < 1 or size > 100:
        return response_.error(message='参数错误: 页码必须大于0，每页数量在1-100之间', code=400)

    try:
        with Session(DevelopmentConfig.engine) as session:
            # 创建基础查询
            query = session.query(model.EqCategory)

            # 添加名称过滤条件（模糊查询）
            if category_name and category_name.strip():
                query = query.filter(
                    model.EqCategory.category_name.ilike(f'%{category_name.strip()}%')
                )

            # 添加状态过滤条件
            if status and status in ('0', '1'):
                query = query.filter(
                    model.EqCategory.status == int(status)
                )
            elif status:  # 无效的状态值
                return response_.error(message='参数错误: 状态值必须为 0 或 1', code=400)

            # 计算总数和总页数
            total_items = query.count()
            # 计算分页信息
            total_pages = max(1, math.ceil(total_items / size))

            # 应用分页
            offset_val = (page - 1) * size
            categories = query.offset(offset_val).limit(size).all()

            # 转换对象为字典
            category_data = [category.to_dict() for category in categories]
            response = {
                'list': category_data,
                'count': total_items,
                'Pagination': {
                    'page': page,
                    'size': size,
                    'total_pages': total_pages
                }
            }
            return response_.success(data=response)

    except Exception as e:
        return response_.error(message=f'服务器错误: {str(e)}', code=500)

    finally:
        if session:
            session.close()


@devices.route('/category/<int:category_id>', methods=['PUT'])
@verification.token_required
def update_category(current_user, category_id):
    """ 修改设备类别 """
    # 验证输入数据
    if not request.json:
        return response_.error(message='请求体必须为JSON格式', code=400)

    # 获取并验证要更新的字段
    update_data = request.json

    # 检查是否有可更新的字段
    valid_fields = {'category_name', 'category_ident', 'status', 'remark'}
    if not any(field in update_data for field in valid_fields):
        return response_.error(
            message='至少需要提供category_name, category_ident, status或remark中的一个字段', code=400
        )

    # 验证字段类型和长度
    validation_errors = []

    if 'category_name' in update_data:
        name = update_data['category_name']
        if not isinstance(name, str) or len(name.strip()) == 0:
            validation_errors.append('分类名称必须是非空字符串')
        elif len(name) > 100:
            validation_errors.append('分类名称长度不能超过100个字符')

    if 'category_ident' in update_data:
        ident = update_data['category_ident']
        if not isinstance(ident, str) or len(ident.strip()) == 0:
            validation_errors.append('分类标识必须是非空字符串')
        elif len(ident) > 50:
            validation_errors.append('分类标识长度不能超过50个字符')

    if 'status' in update_data:
        status_val = update_data['status']
        if status_val not in (0, 1):
            validation_errors.append('状态值必须是0(停用)或1(启用)')

    if 'remark' in update_data and update_data['remark'] is not None:
        remark = update_data['remark']
        if not isinstance(remark, str):
            validation_errors.append('备注必须是字符串')
        elif len(remark) > 255:
            validation_errors.append('备注长度不能超过255个字符')

    if validation_errors:
        return response_.error(message=f'参数验证失败: {validation_errors[0]}', code=400)

    try:
        with Session(DevelopmentConfig.engine) as session:
            # 获取要更新的分类
            stmt = select(model.EqCategory).where(model.EqCategory.id == category_id)
            category = session.scalars(stmt).first()

            # 检查分类是否存在
            if not category:
                return response_.error(
                    message=f'未找到ID为{category_id}的设备分类', code=404
                )

            # 如果需要更新category_ident，检查是否唯一
            if 'category_ident' in update_data:
                new_ident = update_data['category_ident']
                if new_ident != category.category_ident:
                    exists_stmt = select(model.EqCategory.id).where(
                        model.EqCategory.category_ident == new_ident,
                        model.EqCategory.id != category_id
                    )
                    if session.execute(exists_stmt).scalar() is not None:
                        return response_.error(
                            message=f'分类标识"{new_ident}"已被其他分类使用', code=409
                        )

            # 应用更新
            if 'category_name' in update_data:
                category.category_name = update_data['category_name'].strip()
            if 'category_ident' in update_data:
                category.category_ident = update_data['category_ident'].strip()
            if 'status' in update_data:
                category.status = update_data['status']
            if 'remark' in update_data:
                # 允许清除备注
                category.remark = update_data['remark'].strip() if update_data['remark'] else None

            # 提交到数据库
            session.commit()

            # 返回更新后的数据
            return response_.success(data=category.to_dict(), message='设备分类更新成功')

    except Exception as e:
        # 记录错误日志
        # app.logger.error(f"更新设备分类失败: {str(e)}", exc_info=True)
        return response_.error(message=f'服务器错误: {str(e)}', code=500)
    finally:
        if session:
            session.close()


@devices.route('/productManagement', methods=['GET'])
def get_products():
    """ 查询商品管理数据 """
    try:
        # 获取查询参数
        product_name = request.args.get('product_name',  type=str)
        status = request.args.get('status', type=int)

        # 验证状态值
        if status is not None and status not in (0, 1):
            return response_.error(message='状态值只能是0或1', code=400)

        # 处理分页参数
        page = request.args.get('page', default=1, type=int)
        per_page = request.args.get('per_page', default=10, type=int)


        offset = (page - 1) * per_page

        with Session(DevelopmentConfig.engine) as session:
            # 构建基础查询
            stmt = select(model.ProductManagement)

            # 添加筛选条件
            if product_name:
                # 实现产品名称的模糊匹配
                stmt = stmt.where(model.ProductManagement.product_name.ilike(f'%{product_name}%'))

            if status is not None:
                stmt = stmt.where(model.ProductManagement.status == status)

            # 计算总数
            total_count = session.execute(
                select(func.count()).select_from(stmt.subquery())
            ).scalar_one()

            # 添加排序和分页
            stmt = stmt.order_by(model.ProductManagement.id.desc())
            stmt = stmt.offset(offset).limit(per_page)

            # 执行查询
            products = session.scalars(stmt).all()

            # 转换为字典格式
            products_data = [product.to_dict() for product in products]

            # 计算总页数
            total_pages = (total_count + per_page - 1) // per_page
            data = {
                'count': total_count,
                'message': '查询成功',
                'list': products_data,
                'pagination': {
                    'total_pages': total_pages,
                    'current_page': page,
                    'per_page': per_page
                    }
                }

            return response_.success(data=data)
    except Exception as e:
        # 记录错误日志
        # app.logger.error(f"查询产品信息失败: {str(e)}", exc_info=True)
        return response_.error(message=f'服务器错误: {str(e)}', code=500)
    finally:
        if session:
            session.close()



@devices.route('/add/productManagement', methods=['POST'])
def add_product():
    """ 新增产品管理 """
    # 获取请求数据
    data = request.get_json()
    if not data:
        return response_.error(message='请求体必须为JSON格式', code=400)

    # 验证必填字段
    required_fields = ['product_name', 'product_type']
    missing_fields = [field for field in required_fields if field not in data or not data[field]]

    if missing_fields:
        return response_.error(
            message=f'缺少必填字段: {", ".join(missing_fields)}',
            code=400
        )

    # 验证字段长度和类型
    validation_errors = []

    # 验证产品名称
    if 'product_name' in data:
        if len(data['product_name']) > 100:
            validation_errors.append('产品名称长度不能超过100个字符')

    # 验证产品类型
    if 'product_type' in data and data['product_type']:
        if len(data['product_type']) > 100:
            validation_errors.append('产品类型长度不能超过100个字符')

    # 验证图片URL
    if 'image' in data and data['image']:
        if len(data['image']) > 255:
            validation_errors.append('图片URL长度不能超过255个字符')
        # 可选：验证URL格式
        elif not data['image'].startswith(('http://', 'https://')):
            validation_errors.append('图片URL格式不正确')

    # 验证状态值
    if 'status' in data:
        status_val = data['status']
        if status_val not in (0, 1):
            validation_errors.append('状态值必须是0(未发布)或1(已发布)')

    if validation_errors:
        return response_.error(
            message=f'参数验证失败: {", ".join(validation_errors)}',
            code=400
        )

    session = None
    try:
        # 创建新产品对象
        new_product = model.ProductManagement(
            product_name=data['product_name'].strip(),
            product_type=data.get('product_type', None),
            image=data.get('image', None),
            status=data.get('status', 0)  # 默认为未发布状态
        )

        # 保存到数据库
        session = Session(DevelopmentConfig.engine)
        session.add(new_product)
        session.commit()

        # 返回新创建的产品信息
        return response_.success(
            message='产品创建成功',
            data=new_product.to_dict()
        )

    except IntegrityError as e:
        # 处理唯一性约束错误
        if "Duplicate entry" in str(e):
            return response_.error(
                message=f'产品名称"{data["product_name"]}"已存在',
                code=409
            )
        else:
            return response_.error(
                message=f'数据库完整性错误: {str(e)}',
                code=500
            )

    except Exception as e:
        # 记录错误日志
        print(f"添加产品失败: {str(e)}")
        return response_.error(
            message=f'服务器错误: {str(e)}',
            code=500
        )

    finally:
        if session:
            session.close()


@devices.route('/update/productManagement/<int:product_id>', methods=['PUT'])
def update_product(product_id):
    """ 修改产品信息 """
    # 获取请求数据
    data = request.get_json()
    if not data:
        return response_.error(message='请求体必须为JSON格式', code=400)

    # 检查是否有可更新的字段
    valid_fields = ['product_name', 'product_type', 'image']
    if not any(field in data for field in valid_fields):
        return response_.error(
            message='至少提供product_name、product_type、image中的一个字段进行更新',
            code=400
        )

    # 验证字段长度和类型
    validation_errors = []

    # 验证产品名称
    if 'product_name' in data:
        if not data['product_name'] or len(data['product_name'].strip()) == 0:
            validation_errors.append('产品名称不能为空')
        elif len(data['product_name']) > 100:
            validation_errors.append('产品名称长度不能超过100个字符')

    # 验证产品类型
    if 'product_type' in data and data['product_type']:
        if len(data['product_type']) > 100:
            validation_errors.append('产品类型长度不能超过100个字符')

    # 验证图片URL
    if 'image' in data and data['image']:
        if len(data['image']) > 255:
            validation_errors.append('图片URL长度不能超过255个字符')
        # 验证URL格式
        elif not data['image'].startswith(('http://', 'https://')):
            validation_errors.append('图片URL格式不正确')

    # 验证状态值
    if 'status' in data:
        status_val = data['status']
        if status_val not in (0, 1):
            validation_errors.append('状态值必须是0(未发布)或1(已发布)')

    if validation_errors:
        return response_.error(
            message=f'参数验证失败: {", ".join(validation_errors)}',
            code=400
        )

    session = None
    try:
        session = Session(DevelopmentConfig.engine)

        # 获取要更新的产品
        product = session.get(model.ProductManagement, product_id)
        if not product:
            return response_.error(
                message=f'找不到ID为{product_id}的产品',
                code=404
            )

        # 检查产品名称唯一性（如果修改了名称）
        if 'product_name' in data and data['product_name'].strip() != product.product_name:
            # 检查是否有同名产品
            stmt = select(model.ProductManagement).where(
                model.ProductManagement.product_name == data['product_name'].strip(),
                model.ProductManagement.id != product_id
            )
            existing_product = session.scalars(stmt).first()
            if existing_product:
                return response_.error(
                    message=f'产品名称"{data["product_name"]}"已被其他产品使用',
                    code=409
                )

        # 更新字段
        if 'product_name' in data:
            product.product_name = data['product_name'].strip()
        if 'product_type' in data:
            # 允许清空产品类型
            product.product_type = data['product_type'].strip() if data['product_type'] else None
        if 'image' in data:
            # 允许清空图片URL
            product.image = data['image'].strip() if data['image'] else None
        if 'status' in data:
            product.status = data['status']

        # 更新数据库
        session.commit()

        # 返回更新后的产品信息
        return response_.success(
            message='产品更新成功',
            data=product.to_dict()
        )

    except Exception as e:
        # 记录错误日志
        print(f"更新产品失败: {str(e)}")
        return response_.error(
            message=f'服务器错误: {str(e)}',
            code=500
        )

    finally:
        if session:
            session.close()


@devices.route('/export/productManagement', methods=['GET'])
def export_products():
    """ 导出产品数据到Excel
    支持两种模式：
    1. 导出全部产品：不提供任何参数
    2. 按产品名称导出多个产品：提供多个product_name参数（支持精确匹配多个产品名称）
    """
    try:
        # 获取多个产品名称参数（支持逗号分隔或多次指定参数）
        product_names = []
        product_name_param = request.args.get('product_name', type=str)
        if product_name_param:
            # 支持逗号分隔的多个产品名称
            product_names = [name.strip() for name in product_name_param.split(',') if name.strip()]

        # 如果没有明确的产品名称，则获取所有同名参数
        if not product_names:
            product_names = request.args.getlist('product_name', type=str)
            # 过滤空值
            product_names = [name.strip() for name in product_names if name.strip()]

        session = None
        try:
            session = Session(DevelopmentConfig.engine)

            # 构建基础查询
            stmt = select(model.ProductManagement)

            # 如果有指定的产品名称，则添加筛选条件
            if product_names:
                stmt = stmt.where(model.ProductManagement.product_name.in_(product_names))

            # 排序
            stmt = stmt.order_by(model.ProductManagement.id.desc())

            # 执行查询
            products = session.scalars(stmt).all()

            # 准备导出的数据
            export_data = []
            for product in products:
                export_data.append({
                    "产品名称": product.product_name,
                    "产品类型": product.product_type or "",
                    "产品图片": product.image or "",
                    "是否发布": "是" if product.status == 1 else "否",
                    "创建时间": product.create_time.strftime('%Y-%m-%d %H:%M:%S') if product.create_time else ""
                })

            # 如果没有数据
            if not export_data:
                return response_.error(message='没有找到符合条件的产品数据', code=404)

            # 创建DataFrame
            df = pd.DataFrame(export_data)

            # 创建Excel文件
            excel_file = BytesIO()

            # 使用ExcelWriter
            with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
                df.to_excel(writer, sheet_name='产品数据', index=False)

                # 获取工作簿和工作表
                workbook = writer.book
                worksheet = writer.sheets['产品数据']

                # 设置列宽
                column_widths = {
                    '产品名称': 30,
                    '产品类型': 20,
                    '产品图片': 50,
                    '是否发布': 10,
                    '创建时间': 20
                }

                for i, col in enumerate(df.columns):
                    col_letter = chr(65 + i)
                    worksheet.set_column(f'{col_letter}:{col_letter}', column_widths.get(col, 15))

                # 添加标题样式
                header_format = workbook.add_format({
                    'bold': True,
                    'align': 'center',
                    'valign': 'vcenter',
                    'bg_color': '#4472C4',
                    'font_color': 'white',
                    'border': 1
                })

                # 应用标题样式
                for col_num, value in enumerate(df.columns.values):
                    worksheet.write(0, col_num, value, header_format)

            # 准备下载
            excel_file.seek(0)
            timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"产品数据_导出_{timestamp}.xlsx"

            # 创建响应
            return send_file(
                excel_file,
                as_attachment=True,
                download_name=filename,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )

        finally:
            if session:
                session.close()

    except Exception as e:
        # 记录错误日志
        print(f"导出产品数据失败: {str(e)}")
        return response_.error(
            message=f'导出失败: {str(e)}',
            code=500
        )




